import flask
from flask import Flask,render_template,request,redirect,url_for
import sqlite3

from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField
from wtforms.validators import DataRequired

#import hongguan
import akshare as ak
import pandas as pd
import numpy as np


app = Flask(__name__)
app.config['SECRET_KEY'] = 'aaa bbb ccc'


#定义类
class NameForm(FlaskForm):
    name = StringField('你叫什么名字?', validators=[DataRequired()])
    submit = SubmitField('提交')


@app.route('/get', methods=['GET', 'POST'])
def index1():
    name = None
    form = NameForm()
    if form.validate_on_submit():
        name = form.name.data
        form.name.data = ''
    return render_template('get.html', form=form, name=name)

#登录页面
@app.route('/login')
def login():
    return render_template('login.html')

#登录校验，成功----index function,不是index.html,失败，回到登录页面
#后端得到前端传回的值？
@app.route('/validate',methods=['POST'])
def validate():
    if request.method=='POST' and request.form['email']=='115704857@qq.com' and request.form['password']=='123':
        #print(request.form['email'])
        email=request.form['email']
        return redirect(url_for('index'))#success
        #return request.form['email']

    return redirect(url_for('login'))

@app.route('/success')
def success():
    return "logged in successfully"


@app.route('/')
def index():
    #email,url=validate()
    #email=flask.request.form['email']
    infos = []
    con = sqlite3.connect("car.db")
    cur = con.cursor()
    sql = "select * from a limit 0,10"
    data = cur.execute(sql)
    #sql 查询结果写入列表
    for item in data:
        infos.append(item)

    cate = []
    num = []
    con = sqlite3.connect("car.db")
    cur = con.cursor()
    sql ='select 品牌,"2021-7"  from a  limit 0,10'
    data1 = cur.execute(sql)
    for item in data1:
        cate.append(str(item[0]))
        num.append(item[1])

    cur.close()
    con.close()

    #macro_cnbs_df = ak.macro_cnbs()
    #print(macro_cnbs_df)
    #df=hongguan.macro_cnbs_df
    #print(df)
    return render_template("index2.html",
                           infos=infos,
                           cate=cate,
                           num=num,
                           )
    #return render_template("index.html")

@app.route('/<page>')
def home(page):
    if page:
        return render_template(page)
    else:
        #return render_template("index.html")
        pass


@app.route('/xqgdp')
def xqgdp():
    return render_template("xianqugdp.html")

@app.route('/wangzhi')
def wangzhi():
    df = pd.read_excel('static/ziyuan.xlsx')
    df = df.dropna()
    wzlist=np.array(df).tolist()
    a=[]
    b=[]
    c=[]
    for i in wzlist:
        a.append(i[0])
        b.append(i[1])
        c.append(i[2])
    b=set(b)
    b=list(b)
    #print(df)
    return render_template('index3.html',wzlist=wzlist,a=a,b=b,c=c)

@app.route('/macro')
def macro():
    df = ak.macro_cnbs()
    df1=df.round(1)#数值保留一位小数
    macro_list=df1.values.tolist()#转为列表
    llist=list(df1)
    #print(macro_list)
    #print(llist)
    return render_template("index1.html",df=macro_list,llist=llist)

@app.route('/uploaded.html',methods=['GET','POST'])
def upload():
    if flask.request.method == 'GET':
        return flask.render_template('upload.html')
    else:
        file = flask.request.files['file']
        if file:
            file.save(file.filename)
            return '亲，文件上传成功！'

if __name__ == '__main__':
    app.run()
